<?php

/**
 * @author Immanuel Bauer | 0626117
 */
class Todo {
	
	/** 
	 * @var int 
	 */
	private $id;
	
	/** 
	 * @var string
	 */
	private $title;
	
	/** 
	 * @var string 
	 */
	private $description;
	
	/** 
	 * @var timestamp
	 */
	private $creationDate;
	
	/** 
	 * @var timestamp
	 */
	private $targetDate;
	
	/**
	 *  @var float
	 */
	private $projectedHours;
	
	/** 
	 * @var int
	 */
	private $creatorID;
	
	/** 
	 * @var string
	 */
	private $status;
	
	/**
	 * Creates a new object of the model and fills it with the given data.
	 * @param string $title
	 * @param string $description
	 * @param timestamp $targetDate
	 * @param float $projectedHours
	 * @param int $creatorID
	 * @param string $status
	 */
	function __construct($title, $description, $targetDate, $projectedHours, $creatorID, $status) {
		$this->id = null;
		$this->title = $title;
		$this->description = $description;
		$this->creationDate = time();
		$this->targetDate = (int)$targetDate;
		$this->projectedHours = (float)$projectedHours;
		$this->creatorID = (int)$creatorID;
		$this->status = $status;
	}
	
	/**
	 * Fetches the todo data for the given id and returns a Todo object with the Todos data.
	 * Returns null if the Todo doesn't exist.
	 * 
	 * @param int $id
	 * @return Todo
	 */
	public static function getTodo($id){
		$qry = mysql_query('SELECT * FROM '.TABLE_PREFIX.'todos WHERE id = \''.mysql_real_escape_string($id).'\';');
		$row = mysql_fetch_assoc($qry);
		if(isset($row['id'])){
			$todo = new Todo($row['title'], $row['description'], $row['target_date'], $row['projected_hours'], $row['creator'], $row['status']);	
			$todo->setID($row['id']);
			$todo->setCreationDate($row['creation_date']);
			return $todo;
		} else {
			return null;
		}
	}
	
	/**
	 * Fetches the todos matching the userID and/or listID and returns them in an array
	 * 
	 * @param int $userID
	 * @param int $listID
	 * @param int $from
	 * @param int $rows
	 * @param string $sort "s", "pH", "tD" or "cD" (default)
	 * @param string $order "asc" or "desc" (default)
	 * 
	 * @return array<Todo>
	 */
	public static function getTodos($userID, $listID, $from = 0, $rows = 30, $sort = 'cD', $order = 'desc', $includingPast = true){
		$a = array();
		
		if(!is_null($userID) || !is_null($listID)){
			$where = '';
			if(!is_null($userID)){
				$where .= '(t.creator = \''.mysql_real_escape_string($userID).'\' OR tu.user = \''.mysql_real_escape_string($userID).'\') ';
			}
			if(!is_null($listID)){
				if($where != '') $where .= 'AND ';
				$where .= 'tl.list = \''.mysql_real_escape_string($listID).'\'';
			}
			if(!$includingPast){
				if($where != '') $where .= ' AND ';
				$where .= 't.target_date >= '.time().'';
			}
			
			switch($sort){
				case 's': $sort = 't.status'; break;
				case 'pH': $sort = 't.projected_hours'; break;
				case 'tD': $sort = 't.target_date'; break;
				default: $sort = 't.creation_date'; break;
			}
			
			switch($order){
				case 'asc': $order = 'ASC'; break;
				case 'desc': $order = 'DESC'; break;
				case 'a': $order = 'ASC'; break;
				case 'd': $order = 'DESC'; break;
				default: $order = 'DESC'; break;
			}
			
			if($where != '') $where = 'WHERE '.$where;
			
			$qry = mysql_query('SELECT * FROM '.TABLE_PREFIX.'todos AS t LEFT JOIN '.TABLE_PREFIX.'todo_list AS tl ON tl.todo = t.id LEFT JOIN '.TABLE_PREFIX.'todo_user AS tu ON tu.todo = t.id '.$where.' GROUP BY t.id ORDER BY '.$sort.' '.$order.' LIMIT '.mysql_real_escape_string($from).','.mysql_real_escape_string($rows).';');
			
			while($row = mysql_fetch_assoc($qry)){
				$todo = new Todo($row['title'], $row['description'], $row['target_date'], $row['projected_hours'], $row['creator'], $row['status']);	
				$todo->setID($row['id']);
				$todo->setCreationDate($row['creation_date']);
				array_push($a, $todo);
			}
		}
		
		return $a;
	}
	
	/**
	 * Retrieves the count of todos matching the parameters 
	 * @param int $listID
	 * @param int $userID
	 * @param string $status oS(on schedule), bS(behind schedule), n(new) or w(waiting)
	 * 
	 * @return int
	 */
	public static function getTodoCount($listID = null, $userID = null, $status = null, $includingPast = true){
		
		$where = '';
		
		if(!is_null($userID)){
			$where .= '(t.creator = \''.mysql_real_escape_string($userID).'\' OR tu.user = \''.mysql_real_escape_string($userID).'\') ';
		}
		
		if(!is_null($listID)){
			if($where != '') $where .= 'AND ';
			$where .= 'tl.list = \''.mysql_real_escape_string($listID).'\'';
		}
		
		if(!is_null($status) && $status != ''){
			if($where != '') $where .= ' AND ';
			$where .= 't.status = \''.mysql_real_escape_string($status).'\'';
		}
		
		if(!$includingPast){
			if($where != '') $where .= ' AND ';
			$where .= 't.target_date >= '.time().'';
		}
		
		if($where != '') $where = 'WHERE '.$where;
		
		$count = mysql_fetch_array(mysql_query('SELECT count(DISTINCT t.id) FROM '.TABLE_PREFIX.'todos AS t LEFT JOIN '.TABLE_PREFIX.'todo_list AS tl ON tl.todo = t.id LEFT JOIN '.TABLE_PREFIX.'todo_user AS tu ON tu.todo = t.id '.$where.';'));
		return (int)$count[0];
	}
	
	/**
	 * Links the todo with the given todoID and the list with the given listID
	 * @param int $todoID
	 * @param int $listID
	 * 
	 * @return boolean true if succesfully saved otherwise false.
	 */
	public static function linkTodoWithList($todoID, $listID){
		//check if link already exists
		$link = mysql_fetch_assoc(mysql_query('SELECT * FROM '.TABLE_PREFIX.'todo_list WHERE todo = \''.mysql_real_escape_string($todoID).'\' AND list = \''.mysql_real_escape_string($listID).'\';'));
		
		if(!isset($link['todo'])){
			//check if todo and list exist
			$todo = Todo::getTodo($todoID);
			$list = TodoList::getList($listID);
			if(!is_null($todo) && !is_null($list)){
				//save link
				return mysql_query('INSERT INTO '.TABLE_PREFIX.'todo_list (`todo`,`list`) VALUES (\''.mysql_real_escape_string($todoID).'\', \''.mysql_real_escape_string($listID).'\');');
			} else return false;
		} else return false;
	}
	
	/**
	 * Breaks the link between the todo with the given todoID and the list with the given listID
	 * @param int $todoID
	 * @param int $listID
	 * 
	 * @return boolean true if succesfully broken otherwise false.
	 */
	public static function breakLinkTodoWithList($todoID, $listID){
		//check if link exists
		$link = mysql_fetch_assoc(mysql_query('SELECT * FROM '.TABLE_PREFIX.'todo_list WHERE todo = \''.mysql_real_escape_string($todoID).'\' AND list = \''.mysql_real_escape_string($listID).'\';'));
		
		if(isset($link['todo'])){
			//break link
			return mysql_query('DELETE FROM '.TABLE_PREFIX.'todo_list WHERE todo = \''.mysql_real_escape_string($todoID).'\' AND list = \''.mysql_real_escape_string($listID).'\';');
		} else return false;
	}
	
	/**
	 * Returns an array of all Todos linked with the list wit hthe given id
	 * 
	 * @param int $listID
	 * 
	 * @return array<Todo>
	 */
	public static function getTodosInList($listID){
		$a = array();
		
		$qry = mysql_query('SELECT * FROM '.TABLE_PREFIX.'todos AS t JOIN '.TABLE_PREFIX.'todo_list AS tl ON t.id = tl.todo WHERE tl.list = \''.mysql_real_escape_string($listID).'\' ORDER BY t.target_date DESC;');
		
		while($row = mysql_fetch_assoc($qry)){
			$todo = new Todo($row['title'], $row['description'], $row['target_date'], $row['projected_hours'], $row['creator'], $row['status']);	
			$todo->setID($row['id']);
			$todo->setCreationDate($row['creation_date']);
			array_push($a, $todo);
		}
		
		return $a;
	}

	/**
	 * Links the todo with the given todoID and the user with the given userID
	 * @param int $todoID
	 * @param int $userID
	 * @param int $role
	 * 
	 * @return boolean true if succesfully saved otherwise false.
	 */
	public static function linkTodoWithUser($todoID, $userID, $role){
		//check if link already exists
		$link = mysql_fetch_assoc(mysql_query('SELECT * FROM '.TABLE_PREFIX.'todo_user WHERE todo = \''.mysql_real_escape_string($todoID).'\' AND user = \''.mysql_real_escape_string($userID).'\';'));
		
		if(!isset($link['todo'])){
			//check if todo and list exist
			$todo = Todo::getTodo($todoID);
			$user = User::getUser($userID);
			if(!is_null($todo) && !is_null($user)){
				//save link
				return mysql_query('INSERT INTO '.TABLE_PREFIX.'todo_user (`todo`,`user`,`role`) VALUES (\''.mysql_real_escape_string($todoID).'\', \''.mysql_real_escape_string($userID).'\',  \''.mysql_real_escape_string($role).'\');');
			} else return false;
		} else {
			//try to change the role
			return mysql_query('UPDATE '.TABLE_PREFIX.'todo_user SET role = \''.mysql_real_escape_string($role).'\' WHERE todo = \''.mysql_real_escape_string($todoID).'\' AND user = \''.mysql_real_escape_string($userID).'\';');
		}
	}
	
	/**
	 * Breaks the link between the todo with the given todoID and the user with the given userID
	 * @param int $todoID
	 * @param int $userID
	 * 
	 * @return boolean true if succesfully broken otherwise false.
	 */
	public static function breakLinkTodoWithUser($todoID, $userID){
		//check if link exists
		$link = mysql_fetch_assoc(mysql_query('SELECT * FROM '.TABLE_PREFIX.'todo_user WHERE todo = \''.mysql_real_escape_string($todoID).'\' AND user = \''.mysql_real_escape_string($userID).'\';'));
		
		if(isset($link['todo'])){
			//break link
			return mysql_query('DELETE FROM '.TABLE_PREFIX.'todo_user WHERE todo = \''.mysql_real_escape_string($todoID).'\' AND user = \''.mysql_real_escape_string($userID).'\';');
		} else {
			return false;
		}
	}
	
	/**
	 * Saves the current state of the model object into the database
	 * @return boolean true if save was successfull
	 */
	public function saveToDB(){
		if(is_null($this->id)){
			//insert
			$qry = mysql_query('INSERT INTO '.TABLE_PREFIX.'todos (`id`, `title`, `description`, `creation_date`, `target_date`, `projected_hours`, `creator`, `status`) VALUES (
				\'\',
				\''.mysql_real_escape_string($this->title).'\',
				\''.mysql_real_escape_string($this->description).'\',
				\''.mysql_real_escape_string($this->creationDate).'\',
				\''.mysql_real_escape_string($this->targetDate).'\',
				\''.mysql_real_escape_string($this->projectedHours).'\',
				\''.mysql_real_escape_string($this->creatorID).'\',
				\''.mysql_real_escape_string($this->status).'\'
			);');
			if($qry){
				$qry = mysql_query('SELECT id FROM '.TABLE_PREFIX.'todos
					 WHERE `title` = \''.mysql_real_escape_string($this->title).'\'
					 	AND `description` = \''.mysql_real_escape_string($this->description).'\' 
					 	AND `creation_date` = \''.mysql_real_escape_string($this->creationDate).'\'
					 	AND `creator` = \''.mysql_real_escape_string($this->creatorID).'\'
				;');
				$row = mysql_fetch_assoc($qry);
				if(isset($row['id'])){
					$this->id = (int)$row['id'];
					return true;
				} else{
					return false;
				}
			} else {
				return false;
			}
		} else {
			//update
			$qry = mysql_query('UPDATE '.TABLE_PREFIX.'todos SET 
				`title` = \''.mysql_real_escape_string($this->title).'\',
				`description` = \''.mysql_real_escape_string($this->description).'\',
				`target_date` = \''.mysql_real_escape_string($this->targetDate).'\',
				`projected_hours` = \''.mysql_real_escape_string($this->projectedHours).'\',
				`creator` = \''.mysql_real_escape_string($this->creatorID).'\',
				`status` = \''.mysql_real_escape_string($this->status).'\' 
			WHERE id = \''.mysql_real_escape_string($this->id).'\';');
			return $qry;
		}
	}
	
	/**
	 * Removes the model object's corresponding row in the database and returns true if successfull.
	 * @return boolean
	 */
	public function removeFromDB(){
		if(!is_null($this->id)){
			return mysql_query('DELETE FROM '.TABLE_PREFIX.'todos WHERE id = \''.mysql_real_escape_string($this->id).'\';');
		} else {
			return true;
		}
	}
	
/* ####### GETTER ####### */
	
	/**
	 * The id of the User who created this Todo.
	 * 
	 * @return int
	 */
	public function getCreatorID(){
		return (int)$this->creatorID;	
	}
	
	/**
	 * The Todo's id.
	 * @return int
	 */
	public function getID(){
		return (int)$this->id;
	}
	
	/**
	 * The Todo's title.
	 * @return string
	 */
	public function getTitle(){
		return $this->title;
	}
	
	/**
	 * The Todo's description.
	 * @return string
	 */
	public function getDescription(){
		return $this->description;
	}
	
	/**
	 * The Todo's creation date as php timestamp
	 * @return timestamp
	 */
	public function getCreationDate(){
		return (int)$this->creationDate;
	}
	
	/**
	 * The Todo's target date as php timestamp
	 * @return timestamp
	 */
	public function getTargetDate(){
		return (int)$this->targetDate;
	}
	
	/**
	 * The Todo's projected hours.
	 * @return float
	 */
	public function getProjectedHours(){
		return (float)$this->projectedHours;
	}
	
	/**
	 * The Todo's satus.
	 * @return string
	 */
	public function getStatus(){
		return $this->status;
	}

/* ####### SETTER ####### */
	
	/**
	 * Sets the id of the User who created this Todo.
	 * @param int $id
	 */
	private function setCreatorID($id){
		$this->creatorID = (int)$id;	
	}
	
	/**
	 * Sets the Todo's id.
	 * @param int $id;
	 */
	private function setID($id){
		$this->id = (int)$id;
	}
	
	/**
	 * Sets the Todo's title.
	 * @param string $title
	 */
	public function setTitle($title){
		$this->title = $title;
	}
	
	/**
	 * Sets the Todo's description.
	 * @param string $description
	 */
	public function setDescription($description){
		$this->description = $description;
	}

	/**
	 * Sets the Todo's creation date as php timestamp
	 * @param timestamp $date
	 */
	private function setCreationDate($date){
		$this->creationDate = (int)$date;
	}
	
	/**
	 * Sets the Todo's target date as php timestamp
	 * @param timestamp $date
	 */
	public function setTargetDate($date){
		$this->targetDate = (int)$date;
	}
	
	/**
	 * Sets the Todo's projected hours.
	 * @param float $hours
	 */
	public function setProjectedHours($hours){
		$this->projectedHours = (float)$hours;
	}
	
	/**
	 * Sets the Todo's satus.
	 * @param string $status oS(on schedule), bS(behind schedule), n(new), w(waiting) or c(complete)
	 */
	public function setStatus($status){
		$this->status = $status;
	}
	
}

?>